<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */

/**
 * FILE: extjs-grid.php
 * This file is designed as the handler for all Ajax calls to support the ExtJS 4.1.X Ext.Grid.GridPanel component.
 * The following are the parameters recognized.
 *     u               - the username                     (required on all queries)  **FUTURE/NOT YET IMPLEMENTED**
 *     p               - the password                     (required on all queries)  **FUTURE/NOT YET IMPLEMENTED**
 *     database        - the database (a connection)      (required on all queries)
 *     schema          - the schema                       (required on all queries)
 *     table           - the table to operate on          (required on all queries)
 *     method          - "get_hashes", "get_rows"
 *     columns         - comma-separated list of columns  (required on all queries)
 *     p-{column}      - filter (where clause) parameters (implied =/IN operation)
 *     p-{column}-{op} - filter (where clause) parameters (explicit operation)
 *     start           - the first row number of the query data to be returned to the client (0 is the first row of the result set) (skip over rows before this one and don't return them)
 *                                                        (this is technically optional, but it is sensible to configure your Ext program to always send this value for result set paging)
 *     limit           - the maximum number of rows of the query data to be returned to the client
 *                                                        (this is technically optional, but it is sensible to configure your Ext program to always send this value for result set paging)
 *     sort            - json-encoded array of sort columns (e.g. "alt_city_nm.asc,zip.desc")
 *     neat            - used for debugging, this puts extra newlines
 * */

header('Content-type: text/plain');

require_once('applib/App.php');

class Application {
    function handle_request ($options) {
        try {
            $context = App::context($options);

            if (! isset($_REQUEST['database'])) throw new \Exception('ERROR: the "database" parameter must be supplied');
            $database  = $_REQUEST['database'];
            if (! preg_match('/^[a-zA-Z0-9_]+$/', $database)) throw new \Exception('ERROR: the "database" parameter contains illegal characters (must be letters, digits, and underscores)');
            
            if (! isset($_REQUEST['schema'])) throw new \Exception('ERROR: the "schema" parameter must be supplied');
            $schema   = $_REQUEST['schema'];
            if (! preg_match('/^[a-zA-Z0-9_]+$/', $schema)) throw new \Exception('ERROR: the "schema" parameter contains illegal characters (must be letters, digits, and underscores)');
            
            if (! isset($_REQUEST['table'])) throw new \Exception('ERROR: the "table" parameter must be supplied');
            $table   = $_REQUEST['table'];
            if (! preg_match('/^[a-zA-Z0-9_]+$/', $table)) throw new \Exception('ERROR: the "table" parameter contains illegal characters (must be letters, digits, and underscores)');
            
            if (! isset($_REQUEST['columns'])) throw new \Exception('ERROR: the "columns" parameter must be supplied');
            $columns = $_REQUEST['columns'];
            if (! preg_match('/^[a-zA-Z0-9_,]+$/', $columns)) throw new \Exception('ERROR: the "columns" parameter contains illegal characters (columns must be letters, digits, and underscores, separated by commas)');

            $db = $context->database($database);
            $matches = Array();

            $results = Array();
            $results['success'] = true;   # assume we are going to succeed, whatever it is we are doing

            ###################################################################################################
            # Perform Actions
            ###################################################################################################

            if (isset($_REQUEST['record'])) {
                # do nothing. not sure what this is for.
                throw new \Exception('ERROR: the "record" parameter was detected and we dont know what to do with that');
            }
            elseif (isset($_REQUEST['add'])) {
                $record = json_decode($_REQUEST['add'], true);
                throw new \Exception('ERROR: the "add" parameter is not yet implemented');
            }
            elseif (isset($_REQUEST['modifiedRecord'])) {
                $record = json_decode($_REQUEST['modifiedRecord'], true);
                throw new \Exception('ERROR: the "modifiedRecord" parameter is not yet implemented');
            }
            elseif (isset($_REQUEST['deleteRecord'])) {
                $record = json_decode($_REQUEST['deleteRecord'], true);
                throw new \Exception('ERROR: the "deleteRecord" parameter is not yet implemented');
            }
            else {

                ###################################################################################################
                # Data Query
                ###################################################################################################

                $neat    = (isset($_REQUEST['neat'])    && $_REQUEST['neat'])    ? 1 : 0;

                $method = "get_hashes";
                if (isset($_REQUEST['method'])) {
                    $method = $_REQUEST['method'];
                }

                ###################################################################################################
                # Start with the Base Params and Add the User-Selected Dynamic Filters to the Params
                ###################################################################################################

                $params      = Array();

                foreach ($_REQUEST as $var => $value) {
                    if (preg_match('/^p-([a-zA-Z0-9_-]+)$/', $var, $matches)) {
                        $params[$matches[1]] = $value;
                        #echo "param: $matches[1] = $value\n";
                    }
                }

                ###################################################################################################
                # Columns
                ###################################################################################################

                $columns = preg_split('/,/', $columns);   # convert from a comma-separated list to an actual PHP array

                ###################################################################################################
                # Query Options
                ###################################################################################################

                $get_options = array();

                if (isset($_REQUEST['sort']) && $_REQUEST['sort']) {
                    $sorts = explode(',', $_REQUEST['sort']);
                    if (is_array($sorts) && count($sorts) > 0) {
                        $order_by_columns = Array();
                        foreach ($sorts as $sort) {
                            if (preg_match('/^([A-Za-z0-9_]+)\\.(asc|desc)$/i', $sort, $matches)) {
                                $order_by_columns[] = $matches[1] . '.' . strtolower($matches[2]);
                            }
                            elseif (preg_match('/^[A-Za-z0-9_]+$/', $sort, $matches)) {
                                $order_by_columns[] = $sort;
                            }
                            else {
                                throw new \Exception('ERROR: not a valid sort spec ($sort) (must be a column name, optionally followed by ".asc" or ".desc")');
                            }
                        }
                        $get_options['order_by'] = $order_by_columns;
                    }
                }

                if (isset($_REQUEST['start']) && $_REQUEST['start'] > 0) {
                    $start = $_REQUEST['start'];
                    if (! preg_match('/^[0-9]+$/', $start)) throw new \Exception('ERROR: the "start" parameter contains illegal characters (must be a non-negative integer)');
                    $get_options['offset'] = $start;
                }
                if (isset($_REQUEST['limit'])) {
                    $limit = $_REQUEST['limit'];
                    if (! preg_match('/^[0-9]+$/', $limit)) throw new \Exception('ERROR: the "limit" parameter contains illegal characters (must be a positive integer)');
                    if ($limit <= 0) throw new \Exception('ERROR: the "limit" parameter must be a positive integer');
                    $get_options['limit'] = $limit;
                }

                if (isset($options['test_mode']) && $options['test_mode']) {
                    if (isset($_REQUEST['debug_sql'])) {
                        $debug_sql = $_REQUEST['debug_sql'];
                        if (! preg_match('/^-?[0-9]+$/', $debug_sql)) throw new \Exception('ERROR: the "debug_sql" parameter contains illegal characters (must be an integer)');
                        if ($debug_sql) $get_options['debug_sql'] = 2;
                    }
                }
 
                if ($method == 'get_rows') {
                    $data = $db->get_rows("$schema.$table", $params, $columns, $get_options);
                }
                elseif ($method == 'get_hashes') {
                    $data = $db->get_hashes("$schema.$table", $params, $columns, $get_options);
                }
                else {
                    throw new \Exception('ERROR: "method" [$method] is not implemented');
                }
                $results['data'] = $data;
            }

            $json = json_encode($results);

            if ($neat) {
                $json = preg_replace('/\\},\\{/', "},\n {", $json);
                $json = preg_replace('/\\],\\[/', "],\n [", $json);
            }

            echo $json . "\n";
        }
        catch (Exception $e) {
            $this->return_json_error($e->getMessage());
        }
        return(null);
    }

    function return_json_error ($message) {
        if (preg_match("/\n/",$message)) {
            $message = preg_replace("/ *\n */"," ",$message);
        }
        if (preg_match("/\\'/",$message)) {
            $message = preg_replace("/\\'/","\\'",$message);
        }
        echo "{\n";
        echo "    'success': false,\n";
        echo "    'msg': '$message'\n";
        echo "}\n";
    }
}

$application = new Application();
$application->handle_request($options);

